package flst.ipiweb.persistance.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import flst.ipiweb.persistance.dao.IArticleDao;
import flst.ipiweb.transverse.Article;
import flst.ipiweb.transverse.Enseigne;
import flst.ipiweb.transverse.IPIException;

public class ArticleDaoJdbc implements IArticleDao {

	private JdbcUtils jdbcUtils = JdbcUtils.getInstance();

	@Override
	/**
	 * Cette méthode permet de créer un article en base
	 * @param article : l'article à créer
	 */
	public void createArticle(Article article) throws IPIException{

		try {
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteInsert = "INSERT INTO IPII_PRODUCTS (PRODUCT_ID,PRODUCT_NUMBER ,STORE_ID ,SHORT_DESCRIPTION ,DESCRIPTION ,MODEL ,QUANTITY ,PRICE ,PICTURE_NAME ,CREATION_DATE)"
					+ "VALUES(SEQ_PRODUCT.NEXTVAL,?,?,?,?,?,?,?,?,SYSDATE)";
			PreparedStatement vPs = vConnection
					.prepareStatement(vRequeteInsert);
			// product_number
			vPs.setInt(1, article.getReferenceArticle());
			// store_id
			vPs.setInt(2, article.getEnseigne().getIdEnseigne());
			// short_description
			vPs.setString(3, article.getDescriptionCourte());
			// description
			vPs.setString(4, article.getDescriptionLongue());
			// model
			vPs.setString(5, article.getModele());
			// quantity
			vPs.setInt(6, article.getQuantite());
			// price
			vPs.setFloat(7, article.getPrix());
			// picture_name
			vPs.setString(8, article.getImage());
			vPs.executeUpdate();

			vPs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("ArticleException.create.technique");
		}
	}

	@Override
	/**
	 * Cette méthode permet de mettre à jour un article en base
	 * @param article : l'article à mettre à jour
	 */
	public void updateArticle(Article article) throws IPIException{

		try {
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteUpdate = "UPDATE IPII_PRODUCTS SET STORE_ID=?, PRODUCT_NUMBER=?, SHORT_DESCRIPTION=?, DESCRIPTION=?, MODEL=?, QUANTITY=?, PRICE=?, PICTURE_NAME=? WHERE PRODUCT_ID=?";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteUpdate);
			
			// store_id
			vPs.setInt(1, article.getEnseigne().getIdEnseigne());
			//refArticle
			vPs.setInt(2,article.getReferenceArticle());
			// short_description
			vPs.setString(3, article.getDescriptionCourte());
			// description
			vPs.setString(4, article.getDescriptionLongue());
			// model
			vPs.setString(5, article.getModele());
			// quantity
			vPs.setInt(6, article.getQuantite());
			// price
			vPs.setFloat(7, article.getPrix());
			// picture_name
			vPs.setString(8, article.getImage());
			// product_id
			vPs.setInt(9, article.getIdArticle());
			vPs.executeUpdate();

			vPs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("ArticleException.update.technique");
		}
	}

	@Override
	/**
	 * Cette méthode permet de récupérer la liste des articles en base
	 * @return List<Article> : la liste des articles
	 */
	public List<Article> listerArticles() throws IPIException{

		List<Article> listeArticles = new ArrayList<Article>();

		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT * FROM IPII_PRODUCTS p, IPII_STORES s WHERE p.STORE_ID = s.STORE_ID";
			vStmt.execute(vRequeteSelect);
			ResultSet vRs = vStmt.getResultSet();
			while (vRs.next()) {
				Article article = new Article();
				// PRODUCT_ID
				article.setIdArticle(vRs.getInt("PRODUCT_ID"));
				// PRODUCT_NUMBER
				article.setReferenceArticle(vRs.getInt("PRODUCT_NUMBER"));
				// STORE_ID
				Enseigne enseigne = new Enseigne();
				enseigne.setIdEnseigne(vRs.getInt("STORE_ID"));
				enseigne.setLibelle(vRs.getString("STORE_CODE"));
				article.setEnseigne(enseigne);
				// SHORT_DESCRIPTION
				article.setDescriptionCourte(vRs.getString("SHORT_DESCRIPTION"));
				// DESCRIPTION
				article.setDescriptionLongue(vRs.getString("DESCRIPTION"));
				// MODEL
				article.setModele(vRs.getString("MODEL"));
				// QUANTITY
				article.setQuantite(vRs.getInt("QUANTITY"));
				// PRICE
				article.setPrix(vRs.getFloat("PRICE"));
				// PICTURE_NAME
				article.setImage(vRs.getString("PICTURE_NAME"));
				listeArticles.add(article);
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("ArticleException.list.technique");
		}
		return listeArticles;
	}

	@Override
	/**
	 * Cette méthode permet de supprimer un article en base
	 * @param article : l'article à supprimer
	 */
	public void deleteArticle(Article article) throws IPIException{
		try {
			Connection vConnection = jdbcUtils.getConnection();
			String vRequeteDelete = "DELETE FROM IPII_PRODUCTS WHERE PRODUCT_ID=?";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteDelete);
			// product_id
			vPs.setInt(1, article.getIdArticle());
			vPs.executeUpdate();

			vPs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("ArticleException.delete.technique");
		}

	}

	@Override
	/**
	 * Cette méthode permet de générer une nouvelle référence article
	 * @return int : la référence article générée
	 */
	public int GenererRefArticle() {
		
		int refArticle = 0;
		try {
			Connection vConnection = jdbcUtils.getConnection();
			Statement vStmt = vConnection.createStatement();

			String vRequeteSelect = "SELECT nextval('SEQ_PRODUCT_LINE') as REF";
			vStmt.execute(vRequeteSelect);
			ResultSet vRs = vStmt.getResultSet();
			while (vRs.next()) {
				refArticle = vRs.getInt("REF");
			}

			vStmt.close();
			vConnection.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return refArticle;
	}

	@Override
	/**
	 * Cette méthode permet de récupérer en base la liste des articles 
	 * ayant la description courte 'description' passée en paramètre 
	 * @param description : la description courte permettant de filtrer la liste d'articles
	 * @return List<Article> : la liste des articles filtrées par description courte
	 */
	public List<Article> RechercheArticleByDescription(String description) throws IPIException{

		List<Article> listeArticles = new ArrayList<Article>();
		Article article;
		try {
			Connection vConnection = jdbcUtils.getConnection();

			String vRequeteSelect = "SELECT * FROM IPII_PRODUCTS p, IPII_STORES s  WHERE p.STORE_ID = s.STORE_ID AND SHORT_DESCRIPTION LIKE ?";
			PreparedStatement vPs = vConnection
					.prepareStatement(vRequeteSelect);

			// description
			vPs.setString(1, "%" + description + "%");

			ResultSet vRs = vPs.executeQuery();

			while (vRs.next()) {
				article = new Article();
				// PRODUCT_ID
				article.setIdArticle(vRs.getInt("PRODUCT_ID"));
				// PRODUCT_NUMBER
				article.setReferenceArticle(vRs.getInt("PRODUCT_NUMBER"));
				// STORE_ID
				Enseigne enseigne = new Enseigne();
				enseigne.setIdEnseigne(vRs.getInt("STORE_ID"));
				enseigne.setLibelle(vRs.getString("STORE_CODE"));
				article.setEnseigne(enseigne);

				// SHORT_DESCRIPTION
				article.setDescriptionCourte(vRs.getString("SHORT_DESCRIPTION"));
				// DESCRIPTION
				article.setDescriptionLongue(vRs.getString("DESCRIPTION"));
				// MODEL
				article.setModele(vRs.getString("MODEL"));
				// QUANTITY
				article.setQuantite(vRs.getInt("QUANTITY"));
				// PRICE
				article.setPrix(vRs.getFloat("PRICE"));
				// PICTURE_NAME
				article.setImage(vRs.getString("PICTURE_NAME"));
				listeArticles.add(article);
			}
			vRs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("ArticleException.list.technique");
		}
		return listeArticles;
	}

	@Override
	/**
	 * Cette méthode permet de récupérer en base la liste des articles 
	 * ayant le même prix 'price' passée en paramètre 
	 * @param price : le prix des articles recherchés
	 * @return List<Article> : la liste des articles filtrées par prix
	 */
	public List<Article> RechercheArticleByPrice(Double price) throws IPIException{

		List<Article> listeArticles = new ArrayList<Article>();

		try {
			Connection vConnection = jdbcUtils.getConnection();

			String vRequeteSelect = "SELECT * FROM IPII_PRODUCTS p, IPII_STORES s  WHERE p.STORE_ID = s.STORE_ID AND PRICE = ?";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteSelect);

			// par prix
			vPs.setDouble(1, price);
			vPs.execute();
			ResultSet vRs = vPs.getResultSet();
			while (vRs.next()) {
				Article article = new Article();
				// PRODUCT_ID
				article.setIdArticle(vRs.getInt("PRODUCT_ID"));
				// PRODUCT_NUMBER
				article.setReferenceArticle(vRs.getInt("PRODUCT_NUMBER"));
				// STORE_ID
				Enseigne enseigne = new Enseigne();
				enseigne.setIdEnseigne(vRs.getInt("STORE_ID"));
				enseigne.setLibelle(vRs.getString("STORE_CODE"));
				article.setEnseigne(enseigne);

				// SHORT_DESCRIPTION
				article.setDescriptionCourte(vRs.getString("SHORT_DESCRIPTION"));
				// DESCRIPTION
				article.setDescriptionLongue(vRs.getString("DESCRIPTION"));
				// MODEL
				article.setModele(vRs.getString("MODEL"));
				// QUANTITY
				article.setQuantite(vRs.getInt("QUANTITY"));
				// PRICE
				article.setPrix(vRs.getFloat("PRICE"));
				// PICTURE_NAME
				article.setImage(vRs.getString("PICTURE_NAME"));
				listeArticles.add(article);
			}

			vPs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("ArticleException.list.technique");
		}
		return listeArticles;
	}

	@Override
	/**
	 * Cette méthode permet de récupérer en base la liste des articles 
	 * ayant la référence article ref passée en paramètre 
	 * @param ref : la référence article permettant de filtrer la liste d'articles
	 * @return List<Article> : la liste des articles filtrées par référence article
	 */
	public List<Article> RechercheArticleByRef(int ref) throws IPIException{
		List<Article> listeArticles = new ArrayList<Article>();

		try {
			Connection vConnection = jdbcUtils.getConnection();

			String vRequeteSelect = "SELECT * FROM IPII_PRODUCTS p, IPII_STORES s  WHERE p.STORE_ID = s.STORE_ID AND PRODUCT_NUMBER = ?";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteSelect);

			// par refArticle
			vPs.setInt(1, ref);
			vPs.execute();
			ResultSet vRs = vPs.getResultSet();
			while (vRs.next()) {
				Article article = new Article();
				// PRODUCT_ID
				article.setIdArticle(vRs.getInt("PRODUCT_ID"));
				// PRODUCT_NUMBER
				article.setReferenceArticle(vRs.getInt("PRODUCT_NUMBER"));
				// STORE_ID
				Enseigne enseigne = new Enseigne();
				enseigne.setIdEnseigne(vRs.getInt("STORE_ID"));
				enseigne.setLibelle(vRs.getString("STORE_CODE"));
				article.setEnseigne(enseigne);

				// SHORT_DESCRIPTION
				article.setDescriptionCourte(vRs.getString("SHORT_DESCRIPTION"));
				// DESCRIPTION
				article.setDescriptionLongue(vRs.getString("DESCRIPTION"));
				// MODEL
				article.setModele(vRs.getString("MODEL"));
				// QUANTITY
				article.setQuantite(vRs.getInt("QUANTITY"));
				// PRICE
				article.setPrix(vRs.getFloat("PRICE"));
				// PICTURE_NAME
				article.setImage(vRs.getString("PICTURE_NAME"));
				listeArticles.add(article);
			}

			vPs.close();
			vConnection.close();
		} catch (Exception e) {
			throw new IPIException("ArticleException.list.technique");
		}
		return listeArticles;
	}

	@Override
	public Article getArticleById(int id) throws IPIException{
		Article article = new Article();
		try {
			Connection vConnection = jdbcUtils.getConnection();

			String vRequeteSelect = "SELECT * FROM IPII_PRODUCTS p, IPII_STORES s  WHERE p.STORE_ID = s.STORE_ID AND PRODUCT_ID = ?";
			PreparedStatement vPs = vConnection.prepareStatement(vRequeteSelect);
			// par id
			vPs.setInt(1, id);
			ResultSet rs = vPs.executeQuery();
			while(rs.next()){
				article.setIdArticle(rs.getInt("PRODUCT_ID"));
				article.setReferenceArticle(rs.getInt("PRODUCT_NUMBER"));
				article.setDescriptionCourte(rs.getString("SHORT_DESCRIPTION"));
				article.setDescriptionLongue(rs.getString("DESCRIPTION"));
				
				Enseigne enseigne = new Enseigne();
				enseigne.setIdEnseigne(rs.getInt("STORE_ID"));
				enseigne.setLibelle(rs.getString("STORE_CODE"));
				article.setEnseigne(enseigne);
				
				article.setModele(rs.getString("MODEL"));
				article.setImage(rs.getString("PICTURE_NAME"));
				article.setPrix(rs.getFloat("PRICE"));
				article.setQuantite(rs.getInt("QUANTITY"));
			}
		} catch (Exception e) {
			throw new IPIException("Exception.dao.technique");
		}
		return article;
	}
}
